/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sysdental.manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import sysdental.common.GestionDB;
import sysdental.to.PersonaTO;

/**
 *
 * @author Alumno
 */
public class PersonaManager {
    GestionDB cnn=new GestionDB();
    
    //-------------------------Método para listar Personas ------------------
    public List<PersonaTO> listar() throws Exception{
        ArrayList data=new ArrayList();
        String sql="SELECT * FROM sd_persona";
        try{
         cnn.conectar();
         ResultSet rs=cnn.ejecutarConsulta(sql);
         while(rs.next()){
             PersonaTO p=new PersonaTO();
             p.setId_persona(rs.getString("id_persona"));
                   p.setCod_pe(rs.getString("cod_pe"));
                   p.setNom_pe(rs.getString("nom_pe"));
                   p.setDni_pe(rs.getString("dni_pe"));
                   p.setDir_pe(rs.getString("dir_pe"));
                   p.setCel_pe(rs.getString("cel_pe"));
                   p.setEma_pe(rs.getString("ema_pe"));
                   
                   p.setEsp_pe(rs.getString("esp_pe"));
                   p.setFot_pe(rs.getString("fot_pe"));
                   p.setNiv_pe(rs.getString("niv_pe"));
                   p.setUsu_pe(rs.getString("usu_pe"));
                   p.setPas_pe(rs.getString("pas_pe"));
                   p.setAct_pe(rs.getString("act_pe"));
                 
      
            
             data.add(p);
         }
         rs.close();
         cnn.desconectar();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
    return data;            
    }
    
    //------------------------- Método para agregar Persona
    public void agregar(PersonaTO data) throws Exception{
        String sql="INSERT INTO sd_persona (id_persona,cod_pe,nom_pe,dni_pe,dir_pe,cel_pe,ema_pe,fecnac_pe,esp_pe,fot_pe,niv_pe,usu_pe,pas_pe,act_pe,fec_pe) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        Connection c=null;
        try{
            c=cnn.conectar();
            c.setAutoCommit(false);
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setString(1,data.getId_persona());
                ps.setString(2, data.getCod_pe());
                ps.setString(3, data.getNom_pe());
                ps.setString(4, data.getDni_pe());
                ps.setString(5, data.getDir_pe());
                ps.setString(6, data.getCel_pe());
                ps.setString(7, data.getEma_pe());
                ps.setString(8, data.getFecnac_pe());
                ps.setString(9, data.getEsp_pe());
                ps.setString(10, data.getFot_pe());
                ps.setString(11, data.getNiv_pe());
                ps.setString(12, data.getUsu_pe());
                ps.setString(13, data.getPas_pe());
                ps.setString(14, data.getAct_pe());
                ps.setString(15, data.getFec_pe());
             
               
                
                ps.executeUpdate();
                c.commit();
            }
            
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        }finally{
            cnn.desconectar();
        }
    }
    
    //-------------------------Buscar por Id persona para Editar--------
    public PersonaTO buscarPorId(Object id) throws Exception{
        PersonaTO t=null;
        String sql="SELECT * FROM sd_persona WHERE id_persona="+id;
        try{
            cnn.conectar();
            ResultSet rs=cnn.ejecutarConsulta(sql);
            if(rs.next()){
                t=new PersonaTO();
                t.setId_persona(rs.getString("id_persona"));
                   t.setCod_pe(rs.getString("Cod_pe"));
                   t.setNom_pe(rs.getString("nom_pe"));
                   t.setDni_pe(rs.getString("dni_pe"));
                   t.setDir_pe(rs.getString("dir_pe"));
                   t.setCel_pe(rs.getString("cel_pe"));
                   t.setEma_pe(rs.getString("ema_pe"));
                   t.setFecnac_pe(rs.getString("fecnac_pe"));
                   t.setEsp_pe(rs.getString("esp_pe"));
                   t.setFot_pe(rs.getString("fot_pe"));
                   t.setNiv_pe(rs.getString("niv_pe"));
                   t.setUsu_pe(rs.getString("usu_pe"));
                   t.setPas_pe(rs.getString("pas_pe"));
                   t.setAct_pe(rs.getString("act_pe"));
                   t.setFec_pe(rs.getString("fec_pe"));
            }
            rs.close();
            cnn.desconectar();
        }catch(Exception e){
            JOptionPane.showMessageDialog(null,e.getMessage());
        }        
        return t;
    }
    
    //------------------------- Editar persona --------------
    public void editar(PersonaTO data) throws Exception{
        
String sql="UPDATE sd_persona SET cod_pe=?,nom_pe=?,dni_pe=?,dir_pe=?,cel_pe=?,ema_pe=?,fecnac_pe=?,esp_pe=?,fot_pe=?,niv_pe=?,usu_pe=?,pas_pe=?,act_pe=?,fec_pe=? WHERE id_persona=?";
        Connection c=null;
        try{
            c=cnn.conectar();
            c.setAutoCommit(false);
            PreparedStatement ps=c.prepareStatement(sql);
            
             ps.setString(15,data.getId_persona());
            ps.setString(1, data.getCod_pe());
            ps.setString(2, data.getNom_pe());
            ps.setString(3, data.getDni_pe());
            ps.setString(4, data.getDir_pe());
            ps.setString(5, data.getCel_pe());
            ps.setString(6, data.getEma_pe());
            ps.setString(7, data.getFecnac_pe());
            ps.setString(8, data.getEsp_pe());
            ps.setString(9, data.getFot_pe());
            ps.setString(10, data.getNiv_pe());
            ps.setString(11, data.getUsu_pe());
            ps.setString(12, data.getPas_pe());
            ps.setString(13, data.getAct_pe());
            ps.setString(14, data.getFec_pe());
            ps.executeUpdate();
            c.commit();
            ps.close();
            
        }catch(Exception e){
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        }finally{
            cnn.desconectar();
        }
    }
    
    
    //----------------------  Eliminar Persona ----------------
    public void eliminar(String id) throws Exception{
        try{
            Connection c=cnn.conectar();
            String sql="DELETE FROM sd_persona WHERE id_persona=?";
            PreparedStatement ps=c.prepareStatement(sql);
            ps.setString(1,id);
            ps.executeUpdate();
            ps.close();
            
        }catch(Exception e){
            JOptionPane.showMessageDialog(null,e.getMessage());
        }finally{
            cnn.desconectar();
        }
    }
}
